<?php
namespace App\Models;
use App\Models\BaseModel;
use pdo;
class UserModel extends BaseModel{
	public function __construct(){
		parent::__construct();
		$this->_table="employ";
	}
	public function getUserInfo($name,$pas){
		$sql = sprintf("select * from `%s` where `username` = '%s' and password='%s'", $this -> _table, $name,$pas);
		return $this -> query($sql);
	}
	public function getusermenu($username){
		$sql=sprintf("SELECT menu_id,menu_code,data_url,menu_name,parent_menucode,icon  FROM employ LEFT JOIN menu_role ON employ.roleid=menu_role.roleid LEFT JOIN menu ON menu_role.menuid=menu.menu_id WHERE username='".$username."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function getusername($username){
		$sql=sprintf("SELECT realname,rolename from role LEFT JOIN employ ON role.id=employ.roleid WHERE username='".$username."'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function getannounce(){
		$sql=sprintf("SELECT * FROM announce order by id desc");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function delannoun($id){
		$sql=sprintf("DELETE  FROM announce WHERE id='".$id."'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function delannouns($id){
		$sql=sprintf("DELETE  FROM announce WHERE id in ($id)");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function getflower(){
		$sql=sprintf("SELECT * FROM flower");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function getlunbo(){
		$sql=sprintf("SELECT * FROM lunbolist");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function getxianshi(){
		$sql=sprintf("SELECT * FROM xianshi");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function flowerstock(){
		$sql=sprintf("SELECT * from flowerstock");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function orderfinished(){
		$sql=sprintf("SELECT * FROM `order` WHERE state='已完成'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function orderunfinish(){
		$sql=sprintf("SELECT * FROM `order` WHERE state='未支付'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function distributeunfinish(){
		$sql=sprintf("SELECT * FROM distributed LEFT JOIN `order` ON distributed.orderednumber=`order`.ordernumber WHERE distributed.state='未配送' AND `order`.state='已完成'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function distributedoing(){
		$sql=sprintf("SELECT * FROM distributed LEFT JOIN `order` ON distributed.orderednumber=`order`.ordernumber WHERE distributed.state='正配送' AND `order`.state='已完成'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function distributefinished(){
		$sql=sprintf("SELECT * FROM distributed LEFT JOIN `order` ON distributed.orderednumber=`order`.ordernumber WHERE distributed.state='已配送' AND `order`.state='已完成'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> fetchAll(); 
	}
	public function ordercount(){
		$sql = sprintf("SELECT * FROM `order` WHERE state='未支付'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function distribcount(){
		$sql=sprintf("SELECT * FROM `order` WHERE state='已完成'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function announcecount(){
		$sql=sprintf("SELECT * FROM announce");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function getcustomer(){
		$sql=sprintf("SELECT * FROM customer");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function getemployee(){
		$sql=sprintf("SELECT employ.id,employ.username,employ.email,employ.realname,employ.phone,role.rolename FROM employ LEFT JOIN role on employ.roleid=role.id");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function roleList(){
		$sql=sprintf("SELECT * FROM role");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function getSystem(){
		$sql=sprintf("SELECT * from logbook where ctype=1");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function getBusiness(){
		$sql=sprintf("SELECT * from logbook where ctype=2");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function roletree(){
		$sql=sprintf("SELECT rolename FROM role");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function roleget($rolename){
		$sql=sprintf("SELECT id FROM role where rolename='".$rolename."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function getuserrole($id){
		$sql=sprintf("SELECT * FROM employ where roleid='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth->execute();
	    return  $sth -> fetchAll();
	}
	public function orderdels($id){
		$sql=sprintf("DELETE FROM `order` WHERE id in ($id)");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function orderdel($id){
		$sql=sprintf("DELETE FROM `order` WHERE id='".$id."'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
}